﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Xml;
using System.IO;
using System.Configuration;
using System.Xml.Schema;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;

namespace Agresso.Interface.Layer.Service
{
    public class KofaxInvoiceService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AcrBatcInputService m_AcrBatcInputService;
        ConfigurationSettingService m_ConfigurationSettingService;

        public KofaxInvoiceService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_AcrBatcInputService = new AcrBatcInputService();
            m_ConfigurationSettingService = new ConfigurationSettingService();
        }

        public IList ReadFormXMLFile(string xmlFile)
        {
            IList reslt = new ArrayList();
            KofaxInvoice mHeader = null;
            try
            {
                List<string> quotelist = File.ReadAllLines(xmlFile).ToList();
                List<string> quotelistb = new List<string>();
                StringBuilder sb = new StringBuilder();
               
                int i = 0;

                foreach (string line in quotelist)
                {
                    if (!line.Contains("<!"))
                    {
                        string correctline = line.Replace("kfx:", "");
                        sb.AppendLine(correctline);
                    }
                    i++;
                }
                string txt = sb.ToString();
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(txt);

                mHeader = new KofaxInvoice();
                mHeader.FileName = Path.GetFileName(xmlFile);
                XmlNodeList XMLRELEASE = doc.SelectNodes("XMLRELEASE");
                XmlNodeList KOFAXXML = XMLRELEASE[0].SelectNodes("KOFAXXML");
                XmlNodeList BATCHSTATUS = KOFAXXML[0].SelectNodes("BATCHSTATUS");
                XmlNodeList BATCHCLASS = KOFAXXML[0].SelectNodes("BATCHCLASS");
                mHeader.BatchStatus = BATCHSTATUS[0].InnerText;
                mHeader.BatchClassId = BATCHCLASS[0].Attributes["ID"].Value;
                mHeader.BatchClassName = BATCHCLASS[0].Attributes["NAME"].Value;
                XmlNodeList DOCUMENTS = BATCHCLASS[0].SelectNodes("DOCUMENTS");
                XmlNodeList DOCUMENT = DOCUMENTS[0].SelectNodes("DOCUMENT");
                foreach (XmlNode document in DOCUMENT)
                {
                    KofaxInvoice invoice = new KofaxInvoice();
                    mHeader.AddItem(invoice);
                    reslt.Add(invoice);
                    invoice.DocumentId = document.Attributes["DOCID"].Value;
                    invoice.DocumentClassName = document.Attributes["DOCUMENTCLASSNAME"].Value;
                    XmlNodeList DOCUMENTDATA = document.SelectNodes("DOCUMENTDATA");
                    XmlNodeList DOCUMENTFIELD = DOCUMENTDATA[0].SelectNodes("DOCUMENTFIELD");
                    string partNoS = "";
                    string descriptionS = "";
                    string qtyS = "";
                    string unitPriceS = "";
                    string totalPriceS = "";
                    foreach (XmlNode dataxml in DOCUMENTFIELD)
                    {
                        //KofaxInvoice invoice = new KofaxInvoice();
                        //data.AddItem(invoice);
                       
                        switch (dataxml.Attributes["NAME"].Value)
                        {
                            case "Vendor Code":
                                invoice.VendorCode = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Vendor Name":
                                invoice.VendorName = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Invoice No":
                                invoice.InvoiceNo = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Invoice Date":
                                DateTime result = DateTime.Today;
                                if (!DateTime.TryParse(dataxml.Attributes["VALUE"].Value, out result))
                                    result = ConvertToDateTime(dataxml.Attributes["VALUE"].Value);
                                invoice.InvoiceDate = result;
                                break;
                            case "PO Number":
                                invoice.PoNumber = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Sub Total":
                                invoice.SubTotal = Convert.ToDecimal(dataxml.Attributes["VALUE"].Value);
                                break;
                            case "Tax":
                                invoice.Tax = Convert.ToDecimal(dataxml.Attributes["VALUE"].Value);
                                break;
                            case "Total Amount":
                                invoice.TotalAmount = Convert.ToDecimal(dataxml.Attributes["VALUE"].Value);
                                break;
                            case "Part No":
                                partNoS = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Description":
                                descriptionS = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Qty":
                                qtyS = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Unit Price":
                                unitPriceS = dataxml.Attributes["VALUE"].Value;
                                break;
                            case "Total Price":
                                totalPriceS = dataxml.Attributes["VALUE"].Value;
                                break;
                        }
                    }
                    string[] partNo = partNoS.Split(';');
                    string[] description = descriptionS.Split(';');
                    string[] qty = qtyS.Split(';');
                    string[] unitPrice = unitPriceS.Split(';');
                    string[] totalPrice = totalPriceS.Split(';');
                    int c = 0;
                    foreach (string p in partNo)
                    {
                        KofaxInvoice item = new KofaxInvoice();
                        invoice.AddItem(item);
                        item.PartNo = p;
                        item.Description = description[c];
                        item.Qty = Convert.ToDecimal(qty[c]);
                        item.UnitPrice = Convert.ToDecimal(unitPrice[c]);
                        item.TotalPrice = Convert.ToDecimal(totalPrice[c]);
                        c++;                        
                    }
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            return reslt;

        }
        private DateTime ConvertToDateTime(string p)
        {
            string[] date = p.Split('/');
            return new DateTime(Convert.ToInt32(date[2]), Convert.ToInt32(date[1]), Convert.ToInt32(date[0]));
        }
        void settings_ValidationEventHandler(object sender, ValidationEventArgs e)
        {
          
        }
        public void ImportDataFromXml()
        {
            try
            {
                string maxPostInvoiceFileFolder = ConfigurationManager.AppSettings["KofaxInvoiceFileFolder"];
                string maxPostInvoiceBackUpFileFolder = ConfigurationManager.AppSettings["KofaxInvoiceBackUpFileFolder"];
                string[] files = System.IO.Directory.GetFiles(maxPostInvoiceFileFolder);
                foreach (string file in files)
                {
                    string ext = Path.GetExtension(file);
                    if (ext.ToLower() != ".xml") continue;
                    IList result = null;

                    DeleteLogFile(file + "_log.log");
                    try
                    {
                        result = ReadFormXMLFile(file);
                    }
                    catch (Exception x)
                    {
                        CreateLogFile(file + "_log.log", DateTime.Now + " : " + x.Message);
                    }
                    if (result != null)
                    {
                        foreach(KofaxInvoice ki in result)
                            InsertToDB(ki);
                        string destinationFile = maxPostInvoiceBackUpFileFolder + file.Substring(file.LastIndexOf("\\") + 1);
                        File.Move(file, AssertFileExist(maxPostInvoiceBackUpFileFolder, destinationFile));
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
        }
        public void CreateLogFile(string fileName, string log)
        {
            StreamWriter file = new StreamWriter(fileName);
            file.WriteLine(log);
            file.Close();
        }
        public void DeleteLogFile(string fileName)
        {
            if (File.Exists(fileName))
                File.Delete(fileName);
        }
        public static string AssertFileExist(string destinationFolder, string file)
        {
            if (File.Exists(file))
            {
                string fileName = file.Substring(file.LastIndexOf("\\") + 1);
                string[] files = fileName.Split('.');
                string file1 = files[0];
                string file2 = files[1];
                file = destinationFolder + file1 + "_Copy." + file2;
                file = AssertFileExist(destinationFolder, file);
                return file;
            }
            return file;
        }
        private void InsertToDB(KofaxInvoice result)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(KofaxInvoiceSQL.InsertKofaxInvoice(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (result.LineItems.Count == 0)
                    result.LineItems.Add(result);
                foreach (KofaxInvoice item in result.LineItems)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@BatchClassId", System.Data.SqlDbType.NVarChar).Value = item.BatchClassId;
                    cmd.Parameters.Add("@BatchClassName", System.Data.SqlDbType.NVarChar).Value = item.BatchClassName;
                    cmd.Parameters.Add("@BatchStatus", System.Data.SqlDbType.NVarChar).Value = item.BatchStatus;
                    cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar).Value = item.Description;
                    cmd.Parameters.Add("@DocumentClassName", System.Data.SqlDbType.NVarChar).Value = item.DocumentClassName;
                    cmd.Parameters.Add("@DocumentId", System.Data.SqlDbType.NVarChar).Value = item.DocumentId;
                    cmd.Parameters.Add("@FileName", System.Data.SqlDbType.NVarChar).Value = item.FileName;
                    cmd.Parameters.Add("@InvoiceDate", System.Data.SqlDbType.DateTime).Value = item.InvoiceDate;
                    cmd.Parameters.Add("@InvoiceNo", System.Data.SqlDbType.NVarChar).Value = item.InvoiceNo;
                    cmd.Parameters.Add("@PartNo", System.Data.SqlDbType.NVarChar).Value = item.PartNo;
                    cmd.Parameters.Add("@PoNumber", System.Data.SqlDbType.NVarChar).Value = item.PoNumber;
                    cmd.Parameters.Add("@Qty", System.Data.SqlDbType.Decimal).Value = item.Qty;
                    cmd.Parameters.Add("@SubTotal", System.Data.SqlDbType.Decimal).Value = item.SubTotal;
                    cmd.Parameters.Add("@Tax", System.Data.SqlDbType.Decimal).Value = item.Tax;
                    cmd.Parameters.Add("@TotalAmount", System.Data.SqlDbType.Decimal).Value = item.TotalAmount;
                    cmd.Parameters.Add("@TotalPrice", System.Data.SqlDbType.Decimal).Value = item.TotalPrice;
                    cmd.Parameters.Add("@UnitPrice", System.Data.SqlDbType.Decimal).Value = item.UnitPrice;
                    cmd.Parameters.Add("@VendorCode", System.Data.SqlDbType.NVarChar).Value = item.VendorCode;
                    cmd.Parameters.Add("@VendorName", System.Data.SqlDbType.NVarChar).Value = item.VendorName;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteInterface()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(KofaxInvoiceSQL.SelectAllPendingInfoice(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                IList invoiceResult = new ArrayList();
                IList result = new ArrayList();
                while (rd.Read())
                    invoiceResult.Add(rd[0].ToString());
                rd.Close();
                cmd.CommandText = KofaxInvoiceSQL.SelectAllPendingInfoiceByInvoice();
                foreach (string inv in invoiceResult)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@DocumentId", System.Data.SqlDbType.NVarChar).Value = inv;
                    rd = cmd.ExecuteReader();
                    KofaxInvoice header = null;
                    while (rd.Read())
                    {
                        if (header == null)
                            header = new KofaxInvoice(rd);
                        header.AddItem(new KofaxInvoice(rd));
                    }
                    result.Add(header);
                    rd.Close();
                }
                foreach (KofaxInvoice ki in result)
                {
                    IList acrbatchinputList = new ArrayList();
                    int voucherNumber = GetVoucherRunningNumber();
                    acrbatchinput acrbatchinput_AP = new acrbatchinput();
                    acrbatchinput_AP.voucher_type = "RI";
                    acrbatchinput_AP.trans_type = "AP";
                    acrbatchinput_AP.client = "PT";
                    acrbatchinput_AP.apar_type = "P"; 
                    acrbatchinput_AP.apar_id = ki.VendorCode;
                    acrbatchinput_AP.apar_name = ki.VendorName;
                    acrbatchinput_AP.account = "3000";
                    acrbatchinput_AP.dim_1 = "";
                    acrbatchinput_AP.dim_2 = "";
                    acrbatchinput_AP.dim_3 = "";
                    acrbatchinput_AP.dim_4 = "";
                    acrbatchinput_AP.dim_5 = "";
                    acrbatchinput_AP.dim_6 = "";
                    acrbatchinput_AP.dim_7 = "";
                    acrbatchinput_AP.tax_code = "0";
                    acrbatchinput_AP.tax_system = "";
                    acrbatchinput_AP.currency = "";
                    acrbatchinput_AP.dc_flag = 0;
                    acrbatchinput_AP.cur_amount = 0;
                    acrbatchinput_AP.amount = ki.TotalAmount;
                    acrbatchinput_AP.value_1 = 0;
                    acrbatchinput_AP.value_2 = 0;
                    acrbatchinput_AP.value_3 = 0;
                    acrbatchinput_AP.description = "";
                    acrbatchinput_AP.trans_date = ki.InvoiceDate;
                    acrbatchinput_AP.voucher_date = ki.InvoiceDate;
                    acrbatchinput_AP.voucher_no = voucherNumber;
                    acrbatchinput_AP.ext_inv_ref = ki.InvoiceNo;
                    acrbatchinput_AP.status = "N";
                    //acrbatchinput_AP.due_date = pda.TransactionDate;
                    acrbatchinput_AP.period = 201404;//Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    acrbatchinput_AP.responsible = "DEV01";
                    acrbatchinput_AP.batch_id = ki.BatchClassId;
                    acrbatchinput_AP.interface_ = "BI";
                    //acrbatchinput_AP.disc_date = pda.TransactionDate;
                    acrbatchinput_AP.sequence_no = GetSequenceRunningNumber();
                    acrbatchinputList.Add(acrbatchinput_AP);

                    foreach (KofaxInvoice item in ki.LineItems)
                    {
                        acrbatchinput acrbatchinput_GL = new acrbatchinput();
                        acrbatchinput_GL.voucher_type = "RI";
                        acrbatchinput_GL.trans_type = "GL";
                        acrbatchinput_GL.client = "PT";
                        acrbatchinput_GL.apar_type = "";
                        acrbatchinput_GL.apar_id = "";
                        acrbatchinput_GL.apar_name = "";
                        acrbatchinput_GL.account = "";
                        acrbatchinput_GL.dim_1 = "";
                        acrbatchinput_GL.dim_2 = "";
                        acrbatchinput_GL.dim_3 = "";
                        acrbatchinput_GL.dim_4 = "";
                        acrbatchinput_GL.dim_5 = "";
                        acrbatchinput_GL.dim_6 = "";
                        acrbatchinput_GL.dim_7 = "";
                        acrbatchinput_GL.tax_code = "0";
                        acrbatchinput_GL.tax_system = "";
                        acrbatchinput_GL.currency = "";
                        acrbatchinput_GL.dc_flag = 0;
                        acrbatchinput_GL.cur_amount = -item.TotalPrice;
                        acrbatchinput_GL.amount = 0;
                        acrbatchinput_GL.value_1 = 0;
                        acrbatchinput_GL.value_2 = 0;
                        acrbatchinput_GL.value_3 = 0;
                        acrbatchinput_GL.description = acrbatchinput_AP.description;
                        acrbatchinput_GL.trans_date = ki.InvoiceDate;
                        acrbatchinput_GL.voucher_date = ki.InvoiceDate;
                        acrbatchinput_GL.voucher_no = voucherNumber;
                        acrbatchinput_GL.ext_inv_ref = ki.InvoiceNo;
                        acrbatchinput_GL.status = "N";
                        //acrbatchinput_GL.due_date = pda.TransactionDate;
                        acrbatchinput_GL.period = 201404;// Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                        acrbatchinput_GL.responsible = "DEV01";
                        acrbatchinput_GL.batch_id = ki.BatchClassId;
                        acrbatchinput_GL.interface_ = "BI";
                       // acrbatchinput_GL.disc_date = pda.TransactionDate;
                        acrbatchinput_GL.sequence_no = GetSequenceRunningNumber();
                        acrbatchinputList.Add(acrbatchinput_GL);
                    }
                    //insert ACRBATCHinput
                   // string agrtid = "";
                    foreach (acrbatchinput acr in acrbatchinputList)
                    {
                        m_AcrBatcInputService.Insert(acr);
                        m_AcrBatcInputService.InsertToInterfaceTable(acr);
//agrtid += ";" + acr.agrtid.ToString();
                    }
                    //update status-------
                   // if (agrtid != "")
                   // {
                    foreach (KofaxInvoice item in ki.LineItems)
                    {
                        UpdateStatus("", 1, ((acrbatchinput)acrbatchinputList[0]).batch_id, item.id);
                    }
                   // }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        private void UpdateStatus(string remark, int status, string batch_id, int id)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                SqlCommand cmd = new SqlCommand(KofaxInvoiceSQL.UpdateKofaxInvoiceStatus(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                cmd.Parameters.Add("@batch_id", System.Data.SqlDbType.NVarChar).Value = batch_id;
                cmd.Parameters.Add("@remark", System.Data.SqlDbType.NVarChar).Value = remark;
                cmd.Parameters.Add("@pickup_flag", System.Data.SqlDbType.Int).Value = status;
                cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
        }
        private int GetVoucherRunningNumber()
        {
            ConfigurationSetting runningNumber = m_ConfigurationSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_1) + 1;
            runningNumber.val_1 = number.ToString();
            m_ConfigurationSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_ConfigurationSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_ConfigurationSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
    }
}
